package com.webdemo3.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.webdemo3.bean.UsersBean;

/**
 *  用户操作
 * @author lovemanai
 *
 */
public class UsersDao {
	/**
	 * 用户登陆
	 * 在数据中查找用户名为username的数据
	 * @param username 用户名
	 * @return 查找成功，取出用户名和密码，创建一个UsersBean对象返回
	 */
	public static UsersBean usersLogin(String username) {
		String sql = "select * from webdemo3 where username = " + "'" + username + "'";
		ResultSet rs = DBDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				UsersBean ub = new UsersBean();
				ub.setUsername(rs.getString("username"));
				ub.setPwd(rs.getString("pwd"));
				return ub;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBDao.close(rs);
		}
		return null;
	}
	
	/**
	 * 查询用户
	 * @return 所有用户集合
	 */
	public static List<UsersBean> search() {
		List<UsersBean> usersList = new ArrayList<UsersBean>();
		String sql = "select username,gender,phone,addr,birth,regdate from webdemo3";
		ResultSet rs = DBDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				UsersBean ub = new UsersBean();
				ub.setUsername(rs.getString("username"));
				ub.setGender(rs.getString("gender"));
				ub.setPhone(rs.getString("phone"));
				ub.setAddr(rs.getString("addr"));
				ub.setBirth(new SimpleDateFormat("yyyy-MM-dd").format(rs.getTimestamp("birth")));
				ub.setRegDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(rs.getTimestamp("regdate")));
				usersList.add(ub);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBDao.close(rs);
		}
		return usersList;
	}
	
	/**
	 * 查询用户 ，通过用户名
	 * @param 用户名
	 * @return 查询到的结果封装成一个UsersBean对象
	 */
	public static UsersBean search(String username) {
		String sql = "select * from webdemo3 where username = " + "'" + username + "'";
		ResultSet rs = DBDao.excuteSelect(sql);
		UsersBean ub = null;
		try {
			if(rs.next()) {
				ub = new UsersBean();
				ub.setUsername(rs.getString("username"));
				ub.setPwd(rs.getString("pwd"));
				ub.setGender(rs.getString("gender"));
				ub.setPhone(rs.getString("phone"));
				ub.setAddr(rs.getString("addr"));
				ub.setBirth(rs.getString("birth"));
				ub.setRegDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(rs.getTimestamp("regdate")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBDao.close(rs);
		}
		return ub;
	}
	
	/**
	 * 用户注册
	 * 将传入的UsersBean对象插入到数据库
	 * @param 传入一个UserBean对象
	 * @return 插入成功返回1，失败返回0
	 */
	public static int reg(UsersBean ub) {
		String sql = "insert into webdemo3(username,pwd,gender,phone,addr,birth,regdate) value(?,?,?,?,?,?,now())";
		return DBDao.excuteDML(sql, ub.getUsername(), ub.getPwd(), ub.getGender(),ub.getPhone(), 
				ub.getAddr(), ub.getBirth());
	}
	
	/**
	 * 检查用户名是否重复
	 * @param username
	 * @return 返回一个布尔值，重复返回true，不重复返回false
	 */
	public static boolean checkUsername(String username) {
		boolean userState = false;
		String sql = "select username from webdemo3 where username = " + "'" + username + "'";
		ResultSet rs = DBDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				userState = true;
			} else {
				userState = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBDao.close(rs);
		}
		return userState;
	}
	
	/**
	 * 删除用户
	 * @param username 用户名
	 * @return 删除成功返回1，失败返回0
	 */
	public static int delete(String username) {
		String sql = "delete from webdemo3 where username = " + "'" + username + "'";
		return DBDao.excuteDML(sql);
	}
	
	/**
	 * 更新用户信息
	 * @param 用户名
	 * @return 更新成功返回非0值，失败返回0
	 */
	public static int update(UsersBean ub) {
		String sql = "update webdemo3 set pwd = ?, gender = ?, phone = ?, addr = ?, birth = ? "
				+ "where username = " + "'" + ub.getUsername() + "'";
		return DBDao.excuteDML(sql, ub.getPwd(), ub.getGender(),ub.getPhone(), ub.getAddr(), ub.getBirth());
	}
	
	/**
	 * 获取数据库中总的记录数
	 * @return 数据库中总的记录数 
	 */
	public static int getTotalRecords() {
		int count = 0;
		String sql = "select count(*) count from webdemo3";
		ResultSet rs = DBDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public static List<UsersBean> search(int startPos, int PAGESIZE) {
		List<UsersBean> usersList = new ArrayList<UsersBean>();
		String sql = "select username,gender,phone,addr,birth,regdate from webdemo3 limit " 
					+ startPos + "," + PAGESIZE;
		ResultSet rs = DBDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				UsersBean ub = new UsersBean();
				ub.setUsername(rs.getString("username"));
				ub.setGender(rs.getString("gender"));
				ub.setPhone(rs.getString("phone"));
				ub.setAddr(rs.getString("addr"));
				ub.setBirth(new SimpleDateFormat("yyyy-MM-dd").format(rs.getTimestamp("birth")));
				ub.setRegDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(rs.getTimestamp("regdate")));
				usersList.add(ub);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBDao.close(rs);
		}
		return usersList;
	}
}
